import datetime

from consql import dbCon
from entity.student import Student

def add(student):
    con=None
    try:
        con=dbCon.getCon()
        cur=con.cursor()
        sql='insert into student(id,name,password,phone,money) values(%s,%s,md5(%s),%s,%s)'
        cur.execute(sql,(student.id,student.name,student.password,student.phone,student.money))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def searchallcomputer():
    con=None
    try:
        con=dbCon.getCon()
        cur=con.cursor()
        sql='select * from StudentSComputer'
        cur.execute(sql)
        result=cur.fetchall()
        return result
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)

def searchcomputer(ct,rt):
    con=None
    try:
        con=dbCon.getCon()
        cur=con.cursor()
        sql='select * from StudentSComputer where 1=1'
        if ct:
            sql+=f" and ctname like '%{ct}%'"
        if rt:
            sql+=f" and rtname like '%{rt}%'"
        cur.execute(sql)
        result=cur.fetchall()
        return result
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)

def addorder(sid,cid):
    con=None
    try:
        con=dbCon.getCon()
        cur=con.cursor()
        date=datetime.datetime.now()
        sql='insert into orderpay values (null,%s,%s,0,0,%s,null)'
        cur.execute(sql,(sid,cid,date))
        con.commit()
        return True
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def searchsnum(username):
    con=None
    try:
        con=dbCon.getCon()
        cur=con.cursor()
        sql='select id from student where name=%s'
        cur.execute(sql,(username))
        result=cur.fetchone()
        return result[0]
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)


def endcomputer(sid):
    con=None
    try:
        con=dbCon.getCon()
        cur=con.cursor()
        sql='update orderpay set endtime=now() where sid=%s and endtime is null'
        cur.execute(sql,(sid))
        con.commit()
        return cur.rowcount
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)

def ifstart(sid):
    con=None
    try:
        con=dbCon.getCon()
        cur=con.cursor()
        sql='select * from orderpay where sid=%s and endtime is null'
        cur.execute(sql,(sid))
        return cur.rowcount
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)

def searchself(sid):
    con=None
    try:
        con=dbCon.getCon()
        cur=con.cursor()
        sql='select * from student where id=%s'
        cur.execute(sql,(sid))
        result=cur.fetchone()
        return result
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)

def modifyself(id,password,phone,name):
    con=None
    try:
        con=dbCon.getCon()
        cur=con.cursor()
        sql='update student set password=md5(%s),phone=%s,name=%s where id=%s'
        cur.execute(sql,(password,phone,name,id))
        con.commit()
        return cur.rowcount
    except Exception as e:
        print(e)

def addmoney(num,sid):
    con=None
    try:
        con=dbCon.getCon()
        cur=con.cursor()
        sql='update student set money=money+%s where id=%s'
        cur.execute(sql,(num,sid))
        con.commit()
        return cur.rowcount
    except Exception as e:
        print(e)
        con.rollback()
    finally:
        dbCon.closeCon(con)


def searchorder(sid,oid,ispay):
    con=None
    try:
        con=dbCon.getCon()
        cur=con.cursor()
        sql="select id,cid,case when ispay=1 then '是' else '否' end,cost,starttime,endtime from orderpay where sid=%s"
        if oid:
            sql+=f" and id={oid}"
        if ispay!=-1 and ispay!=None:
            sql+=f" and ispay={ispay}"
        cur.execute(sql,(sid))
        result=cur.fetchall()
        return result
    except Exception as e:
        print(e)
        return False
    finally:
        dbCon.closeCon(con)

def pay(oid):
    con=None
    try:
        con=dbCon.getCon()
        cur=con.cursor()
        
        # 1. 获取订单信息和学生ID
        sql1='select sid,cost from orderpay where id=%s'
        cur.execute(sql1,(oid))
        order_info=cur.fetchone()
        if not order_info:
            return False
        
        sid=order_info[0]
        cost=order_info[1]
        
        # 2. 检查学生余额
        sql2='select money from student where id=%s'
        cur.execute(sql2,(sid))
        student_info=cur.fetchone()
        if not student_info:
            return False
        
        current_money=student_info[0]
        
        # 3. 检查余额是否足够
        if current_money < cost:
            return False
        
        # 4. 扣除余额
        sql3='update student set money=money-%s where id=%s'
        cur.execute(sql3,(cost,sid))
        
        # 5. 更新订单状态为已支付
        sql4='update orderpay set ispay=1 where id=%s'
        cur.execute(sql4,(oid))
        
        con.commit()
        return cur.rowcount
    except Exception as e:
        print(e)
        con.rollback()
        return False
    finally:
        dbCon.closeCon(con)
